import mysql.connector
from mysql.connector import Error

# 数据库配置
DB_CONFIG = {
    'host': '10.2.9.70',
    'port': 3307,
    'user': 'user',
    'password': 'password',
    'database': 'gitlab_data'  # 我们将创建这个数据库
}

def create_database():
    """创建数据库和必要的表"""
    try:
        # 首先创建到MySQL服务器的连接（不指定数据库）
        connection = mysql.connector.connect(
            host=DB_CONFIG['host'],
            port=DB_CONFIG['port'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password']
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建数据库
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_CONFIG['database']}")
            print(f"数据库 {DB_CONFIG['database']} 创建成功或已存在")
            
            # 使用新创建的数据库
            cursor.execute(f"USE {DB_CONFIG['database']}")
            
            # 创建用户表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INT PRIMARY KEY,
                    username VARCHAR(255) NOT NULL,
                    name VARCHAR(255),
                    email VARCHAR(255),
                    created_at DATETIME,
                    last_sign_in_at DATETIME,
                    updated_at DATETIME
                )
            """)
            print("用户表创建成功或已存在")
            
            # 创建项目表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS projects (
                    id INT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    description TEXT,
                    created_at DATETIME,
                    updated_at DATETIME,
                    last_activity_at DATETIME,
                    visibility VARCHAR(50)
                )
            """)
            print("项目表创建成功或已存在")

            # 创建提交表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS commits (
                    id VARCHAR(255) PRIMARY KEY,
                    project_id INT,
                    author_name VARCHAR(255),
                    author_email VARCHAR(255),
                    title TEXT,
                    message TEXT,
                    created_at DATETIME,
                    committed_date DATETIME,
                    stats_additions INT,
                    stats_deletions INT,
                    stats_total INT,
                    FOREIGN KEY (project_id) REFERENCES projects(id)
                )
            """)
            print("提交表创建成功或已存在")
            
            connection.commit()
            print("所有表创建完成")
            
    except Error as e:
        print(f"数据库操作错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

def test_connection():
    """测试数据库连接"""
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"成功连接到MySQL服务器，版本: {db_info}")
            
            cursor = connection.cursor()
            cursor.execute("SELECT DATABASE();")
            database = cursor.fetchone()
            print(f"当前数据库: {database[0]}")
            
    except Error as e:
        print(f"连接错误: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    print("开始测试数据库连接...")
    test_connection()
    print("\n开始创建数据库和表...")
    create_database() 